﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using sqlserverextensions.Helper;


    public partial class UserDefinedFunctions
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dateTime">DATETIME column to format</param>
        /// <param name="statDateTime">reporting month</param>
        /// <param name="zeitraumTyp">type of date range. SMON</param>
        /// <param name="istKumuliert"> 
        /// 0 - don't cumulate values
        /// 1 - cumulate values
        /// </param>
        /// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString Zeitraeume(SqlDateTime dateTime, SqlDateTime statDateTime, SqlString zeitraumTyp, SqlBoolean istKumuliert)
        {
            DateTime dt = dateTime.Value;
            DateTime dtStatistik = statDateTime.Value;

            if (istKumuliert.IsTrue)
            {
                if (zeitraumTyp.Value.Equals("SMON"))
                {
                    return String.Format("01{0:00}-{1:00}{2:00}", GetShortYear(dt), dtStatistik.Month, GetShortYear(dt));
                }
            }
            else
            {
                if (zeitraumTyp.Value.Equals("SMON"))
                {
                    return String.Format("{0:00}{1:00}", dt.Month, GetShortYear(dtStatistik));
                }
            }

            return null;
        }

        private static int GetShortYear(DateTime dateTime)
        {
            return Int32.Parse(dateTime.Year.ToString().Substring(2, 2));
        }
    };
